package org.projetjava.metier.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.projetjava.metier.model.*;

public class NewsDao extends Bdd{
	
	//Objet de connexion
	private Connection con;
	
	//requêtes préparé
	private PreparedStatement prepStatAjoutNews = null;
	private PreparedStatement prepStatModifierNews = null;
	private PreparedStatement prepStatAjoutImageNews = null;
	private PreparedStatement prepStatIdDerniereNews = null;
	private PreparedStatement prepStatListNews = null;
	private PreparedStatement prepStatListNewsLimit = null;
	private PreparedStatement prepStatImageNews = null;
	private PreparedStatement prepStatSupprImages = null;
	private PreparedStatement prepStatSupprHyperText = null;
	private PreparedStatement prepStatSupprNews = null;
	private PreparedStatement prepStatRechNomExact = null;
	private PreparedStatement prepStatRechPartieNom = null;
	private PreparedStatement prepStatRechNomDescription = null;
	private PreparedStatement prepStatNews = null;
	
	//Constructeur de la classe
	public NewsDao()
	{
		super();
		this.con = this.getConnection();
	}
	
	//Ajouter une news
	public int ajouter(News news)
	{
		
		
		try {
			if(this.prepStatAjoutNews == null)
			{
				this.prepStatAjoutNews = this.con.prepareStatement("INSERT INTO News( Libelle_News, Texte_Description_News,Ref_News,Type_Ref_News) VALUES (?,?,?,?)");
			}
			//arguments
			this.prepStatAjoutNews.setString(1, news.getLibelle().toString());
			this.prepStatAjoutNews.setString(2, news.getDescription().toString());
			this.prepStatAjoutNews.setString(3, news.getRef().toString());
			this.prepStatAjoutNews.setString(4, news.getTyperef().toString());
			//execution
			this.prepStatAjoutNews.executeUpdate();
			
			
			if(this.prepStatIdDerniereNews == null)
			{
				this.prepStatIdDerniereNews = this.con.prepareStatement("SELECT Max(ID_News) FROM News");
			}
			
			//execution
			ResultSet resultId = this.prepStatIdDerniereNews.executeQuery();
			int idNews = 0;
			//boucle sur result
			while(resultId.next()){   
				//1 er résultat
				if(resultId.first())
				{
					idNews = (int) resultId.getObject(1);
				}
			}

			//list hypertext
			HyperTextDao daoHypertext = new HyperTextDao();
			daoHypertext.AjoutHyperTextNews(news,idNews);
			
			
			
			//*******************************
			//Ajout IMAGES
			if(this.prepStatAjoutImageNews == null)
			{
				this.prepStatAjoutImageNews = this.con.prepareStatement("INSERT INTO Images(Lien_Images, ID_News) VALUES (?,?)");
			}
			
			for(String list : news.getImages())
			{
				this.prepStatAjoutImageNews.setString(1, list);
				this.prepStatAjoutImageNews.setInt(2, idNews);
				this.prepStatAjoutImageNews.executeUpdate();
			}
			
		} catch (SQLException e) { 
			e.printStackTrace();
			return 1;
	    }
		return 0;
	}
	
	public int modifier(News news)
	{
		try {
			if(this.prepStatModifierNews == null)
			{
				this.prepStatModifierNews = this.con.prepareStatement("UPDATE `News` SET Libelle_News=?,Texte_Description_News=?, Ref_News=?, Type_Ref_News=? WHERE ID_News = ?");
			}
			this.prepStatModifierNews.setString(1, news.getLibelle().toString());
			this.prepStatModifierNews.setString(2, news.getDescription().toString());
			this.prepStatModifierNews.setString(3, news.getRef().toString());
			this.prepStatModifierNews.setString(4, news.getTyperef().toString());
			this.prepStatModifierNews.setInt(5, news.getId());
			this.prepStatModifierNews.executeUpdate();
			
			
			//delete img
			if(this.prepStatSupprImages == null)
			{
				this.prepStatSupprImages= this.con.prepareStatement("DELETE FROM Images WHERE ID_News = ?");
			}
			this.prepStatSupprImages.setInt(1, news.getId());
			this.prepStatSupprImages.executeUpdate();
			
			//delete hypertext
			if(this.prepStatSupprHyperText == null)
			{
				this.prepStatSupprHyperText= this.con.prepareStatement("DELETE FROM LienHyperText WHERE ID_News = ?");
			}
			this.prepStatSupprHyperText.setInt(1, news.getId());
			this.prepStatSupprHyperText.executeUpdate();
			
			//on regénère la liste d'hypertext 
			HyperTextDao daoHypertext = new HyperTextDao();
			daoHypertext.AjoutHyperTextNews(news,news.getId());
			
			//on ajoute les images
			if(this.prepStatAjoutImageNews == null)
			{
				this.prepStatAjoutImageNews = this.con.prepareStatement("INSERT INTO Images(Lien_Images, ID_News) VALUES (?,?)");
			}
			
			for(String list : news.getImages())
			{
				this.prepStatAjoutImageNews.setString(1, list);
				this.prepStatAjoutImageNews.setInt(2, news.getId());
				this.prepStatAjoutImageNews.executeUpdate();
			}
			
			
		} catch (SQLException e) { 
			e.printStackTrace();
			return 1;
	    }
	
		return 0;
	}
	

	public int supprimer(News news)
	{
		try {
			
			//delete img
			if(this.prepStatSupprImages == null)
			{
				this.prepStatSupprImages= this.con.prepareStatement("DELETE FROM Images WHERE ID_News = ?");
			}
			this.prepStatSupprImages.setInt(1, news.getId());
			this.prepStatSupprImages.executeUpdate();
			
			//delete hypertext
			if(this.prepStatSupprHyperText == null)
			{
				this.prepStatSupprHyperText= this.con.prepareStatement("DELETE FROM LienHyperText WHERE ID_News = ?");
			}
			this.prepStatSupprHyperText.setInt(1, news.getId());
			this.prepStatSupprHyperText.executeUpdate();
			
			//delete hypertext
			if(this.prepStatSupprNews == null)
			{
				this.prepStatSupprNews= this.con.prepareStatement("DELETE FROM News WHERE ID_News = ?");
			}
			this.prepStatSupprNews.setInt(1, news.getId());
			this.prepStatSupprNews.executeUpdate();
			
			
		} catch (SQLException e) { 
			e.printStackTrace();
			return 1;
	    }
		return 0;
	}

	
	//Methode qui retourne une list de lieu sans limite
	public List<News> ListNews()
	{
		//création des listes
		List<News> listResultat = new ArrayList<>();
		
		
		
		try {
			//requete pour obtenir la liste des news
			if(this.prepStatListNews == null)
			{
				this.prepStatListNews = this.con.prepareStatement("SELECT Libelle_News,Texte_Description_News,ID_News, Ref_News, Type_Ref_News FROM News ORDER BY ID_News DESC");
			}
			//récupération de l'éxécution
			ResultSet result = this.prepStatListNews.executeQuery();
			
			//boucle sur les résultat
			while(result.next()){    
				
				
				List<HyperText> listHyperText = new ArrayList<>();
				List<String> listImage = new ArrayList<>();
				//On crée un objet news
				News news = new News();

				//on Ajoute le libelle de news et description à l'aide de result
				news.setLibelle(result.getObject(1).toString());
				news.setDescription(result.getObject(2).toString());
				news.setId((int) result.getObject(3));
				news.setRef(result.getObject(4).toString());
				news.setTyperef(result.getObject(5).toString());
				
				
				
				//requete préparé des liens des images
				if(this.prepStatImageNews == null)
				{
					this.prepStatImageNews = this.con.prepareStatement("SELECT Lien_Images FROM `Images` WHERE ID_News =?");
				}
				
				//argument
				this.prepStatImageNews.setInt(1, (int) result.getObject(3));
				
				//on stock dans le resultLienImage
				ResultSet resultLienImage = this.prepStatImageNews.executeQuery();
				while(resultLienImage.next()){
					listImage.add(resultLienImage.getObject(1).toString());
				}
				//on ajoute à l'aide du setteur
				news.setImages(listImage);
				
				HyperTextDao daoHypertext = new HyperTextDao();
				listHyperText = daoHypertext.SelectHyperTextNews((int) result.getObject(3));
				news.setHypertext(listHyperText);
				
				
				
				//On ajoute à la list de news
				listResultat.add(news);
			}
		
		} catch (SQLException e) { 
			e.printStackTrace();
	    }
		
		//on retourne la liste de news
		return listResultat;
	}
	
	//Methode qui retourne une liste de News avec Limit
	public List<News> ListNewsLimit(int nombreResultat)
	{
		//listes
		List<News> listResultat = new ArrayList<>();
		
		
		
		try {
			if(this.prepStatListNewsLimit == null)
			{
				this.prepStatListNewsLimit = this.con.prepareStatement("SELECT Libelle_News,Texte_Description_News,ID_News, Ref_News, Type_Ref_News FROM News ORDER BY ID_News DESC LIMIT 0,?");
			}
			this.prepStatListNewsLimit.setInt(1, nombreResultat);
			ResultSet result = this.prepStatListNewsLimit.executeQuery();
			
			while(result.next()){    
				
				List<String> listImage = new ArrayList<>();
				List<HyperText> listHyperText = new ArrayList<>();
				
				News news = new News();
				
				news.setLibelle(result.getObject(1).toString());
				news.setDescription(result.getObject(2).toString());
				news.setId((int) result.getObject(3));
				news.setRef(result.getObject(4).toString());
				news.setTyperef(result.getObject(5).toString());

				//list hypertext
				HyperTextDao daoHypertext = new HyperTextDao();
				listHyperText = daoHypertext.SelectHyperTextNews((int) result.getObject(3));
				news.setHypertext(listHyperText);
				
				
				//list images
				if(this.prepStatImageNews == null)
				{
					this.prepStatImageNews = this.con.prepareStatement("SELECT Lien_Images FROM `Images` WHERE ID_News =?");
				}
				this.prepStatImageNews.setInt(1, (int) result.getObject(3));
				ResultSet resultLienImage = this.prepStatImageNews.executeQuery();
				while(resultLienImage.next()){
					listImage.add(resultLienImage.getObject(1).toString());
				}
				news.setImages(listImage);
				
				listResultat.add(news);

			}

		} catch (SQLException e) { 
			e.printStackTrace();
	    }
		
		return listResultat;
	}
	
	
	public List<String> rechercheParNomExact(String recherche){
		List<String> listResultat = new ArrayList<>();
			try {
			
				if(this.prepStatRechNomExact == null)
				{
					this.prepStatRechNomExact = this.con.prepareStatement("SELECT Libelle_News FROM News WHERE Libelle_News = ?");
				}
				
				this.prepStatRechNomExact.setString(1, recherche);
			     
			    //L'objet ResultSet contient le r�sultat de la requ�te SQL
			    ResultSet result = this.prepStatRechNomExact.executeQuery();
			      
			      
			     
			    while(result.next()){         
			    	listResultat.add(result.getObject(1).toString());
			    }
	
			    result.close();
		         
			} catch (SQLException e) { 
				e.printStackTrace();
		    }
		return listResultat;
	}
	
	
	public List<String> rechercheParNom(String recherche)
	{
		String like = "%" + recherche + "%";  
		List<String> listResultat = new ArrayList<>();
		
		try {
		
			if(this.prepStatRechPartieNom == null)
			{
				this.prepStatRechPartieNom = this.con.prepareStatement("SELECT Libelle_News FROM News WHERE Libelle_News LIKE ?");
			}
			
			this.prepStatRechPartieNom.setString(1, like);
		     
		    //L'objet ResultSet contient le r�sultat de la requ�te SQL
		    ResultSet result = this.prepStatRechPartieNom.executeQuery();

		    while(result.next()){         
		    	listResultat.add(result.getObject(1).toString());
		    }

		    result.close();
	         
		} catch (SQLException e) { 
			e.printStackTrace();
	    }
	  return listResultat;
		
	}
	
	public List<String> rechercheParLibelleDescription(String recherche)
	{
		String like = "%" + recherche + "%"; 
		List<String> listResultat = new ArrayList<>();
		
		try {
		
			if(this.prepStatRechNomDescription == null)
			{
				this.prepStatRechNomDescription = this.con.prepareStatement("SELECT Libelle_News FROM News WHERE Libelle_News LIKE ? OR Texte_Description_News LIKE ?");
			}
			
			this.prepStatRechNomDescription.setString(1, like);
			this.prepStatRechNomDescription.setString(2, like);
		     
		    //L'objet ResultSet contient le r�sultat de la requ�te SQL
		    ResultSet result = this.prepStatRechNomDescription.executeQuery();

		    while(result.next()){         
		    	listResultat.add(result.getObject(1).toString());
		    }

		    result.close();
	         
		} catch (SQLException e) { 
			e.printStackTrace();
	    }
	  return listResultat;
		
	}
	
	public News selection(String libelle)
	{
		News news = null;
		try {
			
			if(this.prepStatNews == null)
			{
				this.prepStatNews = this.con.prepareStatement("SELECT ID_News, Libelle_News, Texte_Description_News, Ref_News, Type_Ref_News FROM News WHERE Libelle_News = ?");
			}
			this.prepStatNews.setString(1, libelle);
			ResultSet resultNews = this.prepStatNews.executeQuery();
			
			while(resultNews.next()){
				news = new News();
				news.setLibelle(libelle);
				news.setDescription(resultNews.getObject(3).toString());
				news.setId((int) resultNews.getObject(1));
				
				news.setRef(resultNews.getObject(4).toString());
				news.setTyperef(resultNews.getObject(5).toString());
				
				List<HyperText> listHyperText = new ArrayList<>();
				HyperTextDao daoHypertext = new HyperTextDao();
				listHyperText = daoHypertext.SelectHyperTextNews((int) resultNews.getObject(1));
				news.setHypertext(listHyperText);
				
				
				
				List<String> listImage = new ArrayList<>();
				if(this.prepStatImageNews == null)
				{
					this.prepStatImageNews = this.con.prepareStatement("SELECT Lien_Images FROM `Images` WHERE ID_News = ?");
				}
				this.prepStatImageNews.setInt(1, (int) resultNews.getObject(1));
				ResultSet resultLienImage = this.prepStatImageNews.executeQuery();
				while(resultLienImage.next()){
					listImage.add(resultLienImage.getObject(1).toString());
				}
				news.setImages(listImage);
				
			}
			
			
			
		} catch (SQLException e) { 
			e.printStackTrace();
	    }
		
		return news;
		
	}

	
	
	

}
